﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Transport
{
    public class BangGiaTrongNgayControl
    {
        transportEntities db;

        public BangGiaTrongNgayControl()
        {
            db = new transportEntities();
        }

        public List<BangGiaTrongNgay> BangGiaTrongNgay(string IDDichVu)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          where a.bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && e.ten_dich_vu == IDDichVu
                          select new BangGiaTrongNgay
                          {
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).ToList();

            return result;
        }

        public List<BangGiaTrongNgay> GetBangGiaTrongNgay(string IDDichVu)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          where a.bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.ma_dich_vu == IDDichVu
                          select new BangGiaTrongNgay
                          {
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).ToList();

            return result;
        }
        public List<BangGiaTrongNgay> GetBangGiaTrongNgay()
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          where a.bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu
                          select new BangGiaTrongNgay
                          {
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).ToList();

            return result;
        }
        public List<BangGiaTrongNgayNew> GetBangGiaTrongNgayNew(string IDDichVu, int from, int size)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          from f in db.hang_xe
                          where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.ma_dich_vu == IDDichVu
                          select new BangGiaTrongNgayNew
                          {
                              TenDichVu = e.ten_dich_vu,
                              HangXe = f.ten_hang_xe,
                              Hinhanh = b.hinh_anh,
                              MaDichVu = e.id_dich_vu,
                              TinhTrangXe = b.ma_tinh_trang_xe,
                              MaXe = a.bien_so,
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).OrderBy(t => t.HieuXe).Skip(from).Take(size).ToList();

            return result;
        }
        public List<BangGiaTrongNgayNew> GetBangGiaTrongNgayNew(string IDDichVu)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          from f in db.hang_xe
                          where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.ma_dich_vu == IDDichVu
                          select new BangGiaTrongNgayNew
                          {
                              TenDichVu = e.ten_dich_vu,
                              HangXe = f.ten_hang_xe,
                              Hinhanh = b.hinh_anh,
                              MaDichVu = e.id_dich_vu,
                              TinhTrangXe = b.ma_tinh_trang_xe,
                              MaXe = a.bien_so,
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).OrderBy(t => t.HieuXe).ToList();

            return result;
        }
        public List<BangGiaTrongNgayNew> SearchBangGiaTrongNgay(string IDDichVu, int soCho)
        {
            try
            {
                if (soCho == 0)
                {
                    var result = (from a in db.bang_gia_trong_ngay
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.miens
                                  from e in db.dich_vu
                                  from f in db.hang_xe
                                  where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.ma_dich_vu == IDDichVu
                                  select new BangGiaTrongNgayNew
                                  {
                                      TenDichVu = e.ten_dich_vu,
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaDichVu = e.id_dich_vu,
                                      TinhTrangXe = b.ma_tinh_trang_xe,
                                      MaXe = a.bien_so,
                                      LoaiXe = c.so_cho,
                                      HieuXe = c.ten_thuong_hieu,
                                      GiaTien = a.gia_tien,
                                      GiaNgoaiGio = a.gia_ngoai_gio,
                                      GiaTrangTri = a.gia_trang_tri,
                                      Mien = d.ten_mien,
                                      ChiPhiTaiXe = a.chi_phi_tai_xe,
                                      PhiLuuDem = a.phi_luu_dem
                                  }).OrderBy(t => t.HieuXe).ToList();
                    return result;
                }
                else
                {
                    var result = (from a in db.bang_gia_trong_ngay
                                  from b in db.chi_tiet_xe
                                  from c in db.thuong_hieu
                                  from d in db.miens
                                  from e in db.dich_vu
                                  from f in db.hang_xe
                                  where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.ma_dich_vu == IDDichVu && c.so_cho == soCho
                                  select new BangGiaTrongNgayNew
                                  {
                                      TenDichVu = e.ten_dich_vu,
                                      HangXe = f.ten_hang_xe,
                                      Hinhanh = b.hinh_anh,
                                      MaDichVu = e.id_dich_vu,
                                      TinhTrangXe = b.ma_tinh_trang_xe,
                                      MaXe = a.bien_so,
                                      LoaiXe = c.so_cho,
                                      HieuXe = c.ten_thuong_hieu,
                                      GiaTien = a.gia_tien,
                                      GiaNgoaiGio = a.gia_ngoai_gio,
                                      GiaTrangTri = a.gia_trang_tri,
                                      Mien = d.ten_mien,
                                      ChiPhiTaiXe = a.chi_phi_tai_xe,
                                      PhiLuuDem = a.phi_luu_dem
                                  }).OrderBy(t => t.HieuXe).ToList();
                    return result;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BangGiaTrongNgayNew> SearchBangGiaTrongNgayBySoCho(int soCho)
        {
            try
            {
                var result = (from a in db.bang_gia_trong_ngay
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.miens
                              from e in db.dich_vu
                              from f in db.hang_xe
                              where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && c.so_cho == soCho
                              select new BangGiaTrongNgayNew
                              {
                                  HangXe = f.ten_hang_xe,
                                  TenDichVu = e.ten_dich_vu,
                                  Hinhanh = b.hinh_anh,
                                  MaDichVu = e.id_dich_vu,
                                  TinhTrangXe = b.ma_tinh_trang_xe,
                                  MaXe = a.bien_so,
                                  LoaiXe = c.so_cho,
                                  HieuXe = c.ten_thuong_hieu,
                                  GiaTien = a.gia_tien,
                                  GiaNgoaiGio = a.gia_ngoai_gio,
                                  GiaTrangTri = a.gia_trang_tri,
                                  Mien = d.ten_mien,
                                  ChiPhiTaiXe = a.chi_phi_tai_xe,
                                  PhiLuuDem = a.phi_luu_dem
                              }).OrderBy(t => t.HieuXe).ToList();
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BangGiaTrongNgayNew> GetBangGiaTrongNgayNew()
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          from f in db.hang_xe
                          where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu
                          select new BangGiaTrongNgayNew
                          {
                              HangXe = f.ten_hang_xe,
                              TenDichVu = e.ten_dich_vu,
                              Hinhanh = b.hinh_anh,
                              MaDichVu = e.id_dich_vu,
                              TinhTrangXe = b.ma_tinh_trang_xe,
                              MaXe = a.bien_so,
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).OrderBy(t => t.HieuXe).ToList();

            return result;
        }
        public int CountBGTGRecord()
        {
            var Linq_Results =
              (from a in db.bang_gia_trong_ngay
              select a.bien_so).Count();

            return Linq_Results;
        }
        public int CountBGTGRecordBeService()
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from e in db.dich_vu
                          where a.ma_dich_vu == e.id_dich_vu
                          select new BangGiaTrongNgayNew
                          {
                          }).Count();

            return result;
        }
        public List<BangGiaTrongNgayNew> GetBangGiaTrongNgayNew(int from,int to)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          from f in db.hang_xe
                          where a.bien_so == b.bien_so && f.ma_hang_xe == c.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu
                          select new BangGiaTrongNgayNew
                          {
                              HangXe = f.ten_hang_xe,
                              TenDichVu = e.ten_dich_vu,
                              Hinhanh = b.hinh_anh,
                              MaDichVu = e.id_dich_vu,
                              TinhTrangXe = b.ma_tinh_trang_xe,
                              MaXe = a.bien_so,
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).OrderBy(t => t.HieuXe).Skip(from).Take(to).ToList();

            return result;
        }
        public BangGiaTrongNgayNew GetBangGiaByIDCar(string idCar)
        {
            var result = (from a in db.bang_gia_trong_ngay
                          from b in db.chi_tiet_xe
                          from c in db.thuong_hieu
                          from d in db.miens
                          from e in db.dich_vu
                          from f in db.hang_xe
                          where a.bien_so == b.bien_so && c.ma_hang_xe == f.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && a.bien_so == idCar
                          select new BangGiaTrongNgayNew
                          {
                              Hinhanh = b.hinh_anh,
                              HangXe = f.ten_hang_xe,
                              MaDichVu = e.id_dich_vu,
                              TinhTrangXe = b.ma_tinh_trang_xe,
                              MaXe = a.bien_so,
                              LoaiXe = c.so_cho,
                              HieuXe = c.ten_thuong_hieu,
                              GiaTien = a.gia_tien,
                              GiaNgoaiGio = a.gia_ngoai_gio,
                              GiaTrangTri = a.gia_trang_tri,
                              Mien = d.ten_mien,
                              ChiPhiTaiXe = a.chi_phi_tai_xe,
                              PhiLuuDem = a.phi_luu_dem
                          }).ToList();
            return result[0];
        }
        public List<BangGiaTrongNgayNew> GetBangGiaByIDHangXe(string idHangXe)
        {
            try
            {
                var result = (from a in db.bang_gia_trong_ngay
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.miens
                              from e in db.dich_vu
                              from f in db.hang_xe
                              where a.bien_so == b.bien_so && c.ma_hang_xe == f.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && c.ma_hang_xe == idHangXe
                              select new BangGiaTrongNgayNew
                              {
                                  Hinhanh = b.hinh_anh,
                                  HangXe = f.ten_hang_xe,
                                  MaDichVu = e.id_dich_vu,
                                  TinhTrangXe = b.ma_tinh_trang_xe,
                                  MaXe = a.bien_so,
                                  LoaiXe = c.so_cho,
                                  HieuXe = c.ten_thuong_hieu,
                                  GiaTien = a.gia_tien,
                                  GiaNgoaiGio = a.gia_ngoai_gio,
                                  GiaTrangTri = a.gia_trang_tri,
                                  Mien = d.ten_mien,
                                  ChiPhiTaiXe = a.chi_phi_tai_xe,
                                  PhiLuuDem = a.phi_luu_dem
                              }).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public List<BangGiaTrongNgayNew> GetBangGiaByIDHangXe(string idHangXe,int from,int size)
        {
            try
            {
                var result = (from a in db.bang_gia_trong_ngay
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              from d in db.miens
                              from e in db.dich_vu
                              from f in db.hang_xe
                              where a.bien_so == b.bien_so && c.ma_hang_xe == f.ma_hang_xe && b.ma_thuong_hieu == c.ma_thuong_hieu && a.ma_mien == d.ma_mien && a.ma_dich_vu == e.id_dich_vu && c.ma_hang_xe == idHangXe
                              select new BangGiaTrongNgayNew
                              {
                                  Hinhanh = b.hinh_anh,
                                  HangXe = f.ten_hang_xe,
                                  MaDichVu = e.id_dich_vu,
                                  TinhTrangXe = b.ma_tinh_trang_xe,
                                  MaXe = a.bien_so,
                                  LoaiXe = c.so_cho,
                                  HieuXe = c.ten_thuong_hieu,
                                  GiaTien = a.gia_tien,
                                  GiaNgoaiGio = a.gia_ngoai_gio,
                                  GiaTrangTri = a.gia_trang_tri,
                                  Mien = d.ten_mien,
                                  ChiPhiTaiXe = a.chi_phi_tai_xe,
                                  PhiLuuDem = a.phi_luu_dem
                              }).OrderBy(t => t.HieuXe).Skip(from).Take(size).ToList();

                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public int GetCountBangGiaByIDHangXe(string idHangXe)
        {
            try
            {
                var result = (from a in db.bang_gia_trong_ngay
                              from b in db.chi_tiet_xe
                              from c in db.thuong_hieu
                              where a.bien_so == b.bien_so && b.ma_thuong_hieu == c.ma_thuong_hieu && c.ma_hang_xe == idHangXe
                              select new BangGiaTrongNgayNew
                              {

                              }).Count();

                return result;
            }
            catch (Exception)
            {
                return 0;
            }
        }
    }
}
